# DWD层数据聚合到DWS层的操作脚本（增量）

# 需求：能够手动传入参数指定采集的日期。  如果不传入参数要求能够自动的识别前一天的日期
if [ $# -eq 0 ]
then
    DATESTR=`date -d "-1 day" +%Y-%m-%d`
else
    DATESTR=$1
fi

HIVE_HOME="/usr/bin/hive"
YEARSTR=`date -d "${DATESTR}" +%Y`
MONTHSTR=`date -d "${DATESTR}" +%m`
DAYSTR=`date -d "${DATESTR}" +%d`

# 计算季度
month_for_quarter=`date -d "${DATESTR}" +%-m`
QUARTERSTR=$((($month_for_quarter-1)/3+1))

echo "先删除过期的${YEARSTR}年, ${QUARTERSTR}季度, ${MONTHSTR}月数据"
$HIVE_HOME -e "
-- 删除过期年的数据
ALTER TABLE itcast_dws.visit_dws DROP PARTITION(yearinfo='${YEARSTR}', quarterinfo='-1', monthinfo='-1', dayinfo='-1');
-- 删除过期季度的数据
ALTER TABLE itcast_dws.visit_dws DROP PARTITION(yearinfo='${YEARSTR}', quarterinfo='${QUARTERSTR}', monthinfo='-1', dayinfo='-1');
-- 删除过期月的数据
ALTER TABLE itcast_dws.visit_dws DROP PARTITION(yearinfo='${YEARSTR}', quarterinfo='${QUARTERSTR}', monthinfo='${MONTHSTR}', dayinfo='-1');"

echo "执行新数据的计算，计算的日期是：${DATESTR}"

$HIVE_HOME -e "
-- 指标：咨询量（WHERE msg_count > 0）
-- 维度：时间、区域、来源渠道
--动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时ORC压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

-- 二：DWS层开发（地区表开发）
-- 2.1统计某年 某地区的某地区的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    area,
    '-1' AS origin_channel,
    '-1' AS hourinfo,
    yearinfo AS time_str,
    '1' AS groupType,
    '5' AS time_str,
    yearinfo,
    '-1' AS quarterinfo,
    '-1' AS monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}'
GROUP BY yearinfo ,area;
-- 2.2统计某年 某季度 某地区的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    area,
    '-1' AS origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-Q', quarterinfo) AS time_str,
    '1' AS groupType,
    '4' AS time_str,
    yearinfo,
    quarterinfo,
    '-1' AS monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}'
GROUP BY yearinfo, quarterinfo ,area;
-- 2.3统计某年 某季度 某月 某地区的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    area,
    '-1' AS origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-', monthinfo) AS time_str,
    '1' AS groupType,
    '3' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo ,area;
-- 2.4统计某年 某季度 某月 某日 某地区的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    area,
    '-1' AS origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-', monthinfo, '-', dayinfo) AS time_str,
    '1' AS groupType,
    '2' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}' AND dayinfo = '${DAYSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo ,area;
-- 2.5统计某年 某季度 某月 某日 某小时 某地区的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    area,
    '-1' AS origin_channel,
    hourinfo,
    CONCAT(yearinfo, '-', monthinfo, '-', dayinfo, ' ', hourinfo) AS time_str,
    '1' AS groupType,
    '1' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}' AND dayinfo = '${DAYSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo ,area;

-- 三：DWS层开发（来源渠道表开发）
-- 3.1统计某年 某来源渠道的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' as area,
    origin_channel,
    '-1' AS hourinfo,
    yearinfo AS time_str,
    '2' AS groupType,
    '5' AS time_str,
    yearinfo,
    '-1' AS quarterinfo,
    '-1' AS monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}'
GROUP BY yearinfo ,origin_channel;
-- 3.2统计某年 某季度 某来源渠道的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' as area,
    origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-Q', quarterinfo) AS time_str,
    '2' AS groupType,
    '4' AS time_str,
    yearinfo,
    quarterinfo,
    '-1' AS monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}'
GROUP BY yearinfo, quarterinfo ,origin_channel;
-- 3.3统计某年 某季度 某月 某来源渠道的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' as area,
    origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-', monthinfo) AS time_str,
    '2' AS groupType,
    '3' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo ,origin_channel;
-- 3.4统计某年 某季度 某月 某日 某来源渠道的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' as area,
    origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-', monthinfo, '-', dayinfo) AS time_str,
    '2' AS groupType,
    '2' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}' AND dayinfo = '${DAYSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo ,origin_channel;
-- 3.5统计某年 某季度 某月 某日 某小时 某来源渠道的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' as area,
    origin_channel,
    hourinfo,
    CONCAT(yearinfo, '-', monthinfo, '-', dayinfo, ' ', hourinfo) AS time_str,
    '2' AS groupType,
    '1' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}' AND dayinfo = '${DAYSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo ,origin_channel;

-- 四：DWS层开发（总咨询量表开发）
-- 4.1统计某年 的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' AS area,
    '-1' AS origin_channel,
    '-1' AS hourinfo,
    yearinfo AS time_str,
    '3' AS groupType,
    '5' AS time_str,
    yearinfo,
    '-1' AS quarterinfo,
    '-1' AS monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}'
GROUP BY yearinfo;
-- 4.2统计某年 某季度 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' AS area,
    '-1' AS origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-Q', quarterinfo) AS time_str,
    '3' AS groupType,
    '4' AS time_str,
    yearinfo,
    quarterinfo,
    '-1' AS monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}'
GROUP BY yearinfo, quarterinfo;
-- 4.3统计某年 某季度 某月 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' AS area,
    '-1' AS origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-', monthinfo) AS time_str,
    '3' AS groupType,
    '3' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    '-1' AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo;
-- 4.4统计某年 某季度 某月 某日 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' AS area,
    '-1' AS origin_channel,
    '-1' AS hourinfo,
    CONCAT(yearinfo, '-', monthinfo, '-', dayinfo) AS time_str,
    '3' AS groupType,
    '2' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}' AND dayinfo = '${DAYSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo;
-- 4.5统计某年 某季度 某月 某日 某小时 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    COUNT(DISTINCT sid) AS sid_total,
    COUNT(DISTINCT session_id) AS sessionid_total,
    COUNT(DISTINCT ip) AS ip_total,
    '-1' AS area,
    '-1' AS origin_channel,
    hourinfo,
    CONCAT(yearinfo, '-', monthinfo, '-', dayinfo, ' ', hourinfo) AS time_str,
    '3' AS groupType,
    '1' AS time_str,
    yearinfo,
    quarterinfo,
    monthinfo,
    dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0 AND yearinfo = '${YEARSTR}' AND quarterinfo = '${QUARTERSTR}' AND monthinfo = '${MONTHSTR}' AND dayinfo = '${DAYSTR}'
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;
"